package com.hongdee.crm.task.dao.impl;

import com.hongdee.base.basedao.impl.BaseDaoImpl;
import com.hongdee.common.util.DateUtils;
import com.hongdee.crm.Constant;
import com.hongdee.crm.task.dao.ITCrmTaskDao;
import com.hongdee.crm.task.entity.TCrmTask;
import com.hongdee.crm.task.entity.TCrmTaskReal;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * 名  称:TcrmTaskDaoImpl
 * 类  型: class
 * 描  述: 登录相关事项
 * 路  径:com.hongdee.crm.task.dao.impl
 * 作  者:liuze
 * 日  期:2017/2/28 10:23
 */
@Service
public class TcrmTaskDaoImpl extends BaseDaoImpl implements ITCrmTaskDao {

    @Resource
    private SessionFactory sessionFactory;

    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;


    /**
     * @param companyId 公司id
     * @param pcode     参数表pcode
     * @param code      参数表code
     * @return Integer 返回参数信息
     * @throws Exception
     * @desc 查询公司配置参数信息
     * @author liuze
     */
    public Integer beforDays(String companyId, String pcode, String code) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT `value` as v from t_bc_param_set where ");
        sqlStr.append(" code= ? ");
        sqlStr.append("and p_code = ? ");
        sqlStr.append("and company_id = ? ");
        System.out.println(sqlStr);
        Map<String, String> r = this.info(sqlStr.toString(), code, pcode, companyId);
        if (StringUtils.isEmpty(r.get("v")))
            return null;
        String sir = r.get("v");
        return new Integer(sir);
    }

    @Override
    public List<Map<String, Object>> beforDays(String pcode, String code) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT `value` as v,company_id from t_bc_param_set where ");
        sqlStr.append(" code= ? ");
        sqlStr.append("and p_code = ? ");
        sqlStr.append(" group by company_id");
        System.out.println(sqlStr);
        List<Map<String, Object>> lists = this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), code, pcode);
        return lists;
    }


    /**
     * 先查询工单表中2年内有效进厂次数大于等于1次的车辆信息
     * 在排除掉保险表中存在当期保险信息的车辆
     * 联合车辆信息表查询车辆信息插入到任务表中
     * <p>
     * 2017-04-18 修改活跃非活跃
     * 将年份移除，歧义     *
     * 保险活跃非活路
     *
     * @param company_id 公司id
     * @param bfd        提前天数
     * @return List 返回参数信息
     * @throws Exception
     * @desc 处理保险类非活跃数据
     * @author liuze
     * <p>
     * 生成当月的保险活跃非活跃
     */
    @Override
    public List<Map<String, Object>> baoXianFeiHuoYue(String company_id, int bfd, String pcode, String code) throws Exception {
        //当前年-当前月
        String Year = DateUtils.getTimeStr("yyyy");
        String Month = DateUtils.getTimeStr("MM");


        StringBuffer sqlStr = new StringBuffer();
        // 参数列表
        List params = new ArrayList();
        sqlStr.append(" select distinct c.vin,plate_num,c.contact_tel,c.contact_name,c.owner_tel,c.owner_name, ");
        sqlStr.append(" ");
        sqlStr.append(" DATE_FORMAT(DATE_SUB(str_to_date(concat('',from_unixtime(( if(c.guarantee_start_date is null ,if(c.licence_date is null ,c.buy_date,c.licence_date) ,c.guarantee_start_date))/1000,'%Y-%m-%d')),'%Y-%m-%d'),Interval ? DAY),'%m-%d') as txrq,");
        params.add(bfd);
        sqlStr.append(" ");
        sqlStr.append(" concat('',from_unixtime(( if(c.guarantee_start_date is null ,if(c.licence_date is null ,c.buy_date,c.licence_date) ,c.guarantee_start_date))/1000,'%m-%d')) as dqrq from t_crm_car  c where c.company_id =? ");
        params.add(company_id);
        sqlStr.append(" ");
        sqlStr.append(" ");
        //两年内有过有效有效进厂，截止到生成任务时
        sqlStr.append(" and Exists( select 1 from t_crm_car_workorder o where o.company_id = c.company_id  and o.vin = c.vin and o.real_amount > '0.00' and from_unixtime(o.balance_date/1000,'%Y-%m-%d') > '" + DateUtils.towYearAgo() + "'  )");
        sqlStr.append(" ");
        //并且一年内没有两次有效进厂
        sqlStr.append(" and NOT Exists( select 1 from t_crm_car_workorder o where o.company_id = c.company_id  and o.vin = c.vin and o.real_amount > '0.00' and from_unixtime(o.balance_date/1000,'%Y-%m-%d') > '" + DateUtils.oneYearAge() + "'  group by vin  having count(distinct from_unixtime(o.balance_date/1000,'%Y-%m-%d')) > 1 )");

        //不存在月初1号之后有效的保险
        sqlStr.append(" and not Exists (select 1 from `t_crm_car_insurance`  i where i.company_id = c.company_id and  i.vin = c.vin and from_unixtime(i.`contractstopinsurance`/1000,'%Y-%m-%d') > '" + DateUtils.getTimeStr("yyyy-MM-01") + "' )");
        sqlStr.append(" ");
        sqlStr.append(" and DATE_FORMAT(DATE_SUB(str_to_date(concat('',from_unixtime(( if(c.guarantee_start_date is null ,if(c.licence_date is null ,c.buy_date,c.licence_date) ,c.guarantee_start_date)	)/1000,'%Y-%m-%d')),'%Y-%m-%d'),Interval ? DAY),'%m') = '" + Month + "'");
        params.add(bfd);


//        sqlStr.append("SELECT ");
//        sqlStr.append(" DATE_FORMAT( ");
//        sqlStr.append("  DATE_SUB( ");
//        sqlStr.append("   FROM_UNIXTIME((CASE WHEN licence_date IS NULL THEN guarantee_start_date ELSE licence_date END) / 1000), ");
//        sqlStr.append("   INTERVAL ? DAY ");
//        sqlStr.append("  ), ");
//        sqlStr.append("  '%m-%d' ");
//        sqlStr.append(" ) AS txrq, date_format(FROM_UNIXTIME((CASE WHEN licence_date IS NULL THEN guarantee_start_date ELSE licence_date END) / 1000),'%m-%d') as gprq,");
//        sqlStr.append(" t_crm_car.* ");
//        sqlStr.append("FROM ");
//        sqlStr.append(" t_crm_car ");
//        sqlStr.append("WHERE ");
//        sqlStr.append(" company_id = ? ");
//        Constant.REMIND_CLOSE_SQL(sqlStr,pcode,code,"t_crm_car");
//        sqlStr.append("AND ");
//        sqlStr.append(" vin IN ( ");
//        sqlStr.append("  SELECT ");
//        sqlStr.append("   vin ");
//        sqlStr.append("  FROM ");
//        sqlStr.append("   t_crm_car_workorder ");
//        sqlStr.append("  WHERE ");
//        sqlStr.append("   company_id = ? ");
//        sqlStr.append("        AND ");
//        sqlStr.append("   balance_date >= unix_timestamp(DATE_SUB(NOW(), INTERVAL 2 YEAR)) * 1000 ");
//        sqlStr.append("  AND  real_amount>'0.00' ");
//        sqlStr.append("  GROUP BY ");
//        sqlStr.append("   vin ");
//        sqlStr.append("  HAVING ");
//        sqlStr.append("   count(*) >= 1 ");
//        sqlStr.append(" ) ");
//        sqlStr.append("AND vin NOT IN ( ");
//        sqlStr.append(" SELECT ");
//        sqlStr.append("  vin ");
//        sqlStr.append(" FROM ");
//        sqlStr.append("  t_crm_car_insurance ");
//        sqlStr.append(" WHERE ");
//        sqlStr.append("  contractstartinsurance > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 YEAR)) * 1000 ");
//        sqlStr.append("   AND plate_num is not NULL  AND company_id = ? ");
//        sqlStr.append(") ");
//        sqlStr.append("AND DATE_FORMAT( ");
//        sqlStr.append(" DATE_SUB( ");
//        sqlStr.append("  FROM_UNIXTIME((CASE WHEN licence_date IS NULL THEN guarantee_start_date ELSE licence_date END) / 1000), ");
//        sqlStr.append("  INTERVAL ? DAY ");
//        sqlStr.append(" ), ");
//        sqlStr.append(" '%m' ");
//        sqlStr.append(") = DATE_FORMAT(now(), '%m'); ");
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), params.toArray());

    }


    /**
     * 先从工单表中查询有效进场2次及以上车辆信息
     * 排除掉保险表有中当期保险信息的车辆信息
     * 联合车辆信息表查询车辆信息并插入到保险表中
     * 保险活跃客户
     *
     * @param company_id 公司id
     * @param bfd        提前天数
     * @return List 返回参数信息
     * @throws Exception
     * @desc 处理保险类活跃数据
     * @author liuze
     */

    public List<Map<String, Object>> baoXianHuoYue(String company_id, int bfd, String pcode, String code) throws Exception {
        //当前年-当前月
        String Year = DateUtils.getTimeStr("yyyy");
        String Month = DateUtils.getTimeStr("MM");


        StringBuffer sqlStr = new StringBuffer();
        // 参数列表
        List params = new ArrayList();
        sqlStr.append(" select distinct c.vin,plate_num,c.contact_tel,c.contact_name,c.owner_tel,c.owner_name, ");
        sqlStr.append(" ");
        sqlStr.append(" DATE_FORMAT(DATE_SUB(str_to_date(concat('',from_unixtime(( if(c.guarantee_start_date is null ,if(c.licence_date is null ,c.buy_date,c.licence_date) ,c.guarantee_start_date) )/1000,'%Y-%m-%d')),'%Y-%m-%d'),Interval ? DAY),'%m-%d') as txrq,");
        params.add(bfd);
        sqlStr.append(" ");
        sqlStr.append(" concat('',from_unixtime( (if(c.guarantee_start_date is null ,if(c.licence_date is null ,c.buy_date,c.licence_date) ,c.guarantee_start_date) )/1000,'%m-%d')) as dqrq from t_crm_car  c where c.company_id =? ");
        params.add(company_id);
        sqlStr.append(" ");
        sqlStr.append(" ");
        //一年内两次有效进厂，截止到生成任务时
        sqlStr.append(" and Exists( select 1 from t_crm_car_workorder o where o.company_id = c.company_id  and o.vin = c.vin and o.real_amount > '0.00' and from_unixtime(o.balance_date/1000,'%Y-%m-%d') > '" + DateUtils.oneYearAge() + "'  group by vin  having count(distinct from_unixtime(o.balance_date/1000,'%Y-%m-%d')) > 1 )");
        sqlStr.append(" ");
        //不存在月初1号之后有效的保险
        sqlStr.append(" and not Exists (select 1 from `t_crm_car_insurance`  i where i.company_id = c.company_id and  i.vin = c.vin and from_unixtime(i.`contractstopinsurance`/1000,'%Y-%m-%d') > '" + DateUtils.getTimeStr("yyyy-MM-01") + "' )");
        sqlStr.append(" ");
        sqlStr.append(" and DATE_FORMAT(DATE_SUB(str_to_date(concat('',from_unixtime(( if(c.guarantee_start_date is null ,if(c.licence_date is null ,c.buy_date,c.licence_date) ,c.guarantee_start_date)	)/1000,'%Y-%m-%d')),'%Y-%m-%d'),Interval ? DAY),'%m') = '" + Month + "'");
        params.add(bfd);
//        StringBuffer sqlStr = new StringBuffer();
//        sqlStr.append("SELECT ");
//        sqlStr.append(" DATE_FORMAT( ");
//        sqlStr.append("  DATE_SUB( ");
//        sqlStr.append("   FROM_UNIXTIME((CASE WHEN licence_date IS NULL THEN guarantee_start_date ELSE licence_date END) / 1000), ");
//        sqlStr.append("   INTERVAL ? DAY ");
//        sqlStr.append("  ), ");
//        sqlStr.append("  '%m-%d' ");
//        sqlStr.append(" ) AS txrq, date_format(FROM_UNIXTIME((CASE WHEN licence_date IS NULL THEN guarantee_start_date ELSE licence_date END) / 1000),'%m-%d') as gprq,");
//        sqlStr.append(" t_crm_car.* ");
//        sqlStr.append("FROM ");
//        sqlStr.append(" t_crm_car ");
//        sqlStr.append("WHERE ");
////        sqlStr.append(" NOT EXISTS (select * from t_crm_car_remindclose cle where cle.user_carid=t_crm_car.id and " +
////                "cle.company_id=t_crm_car.company_id and cle.vin=t_crm_car.vin) AND ");
//
//        sqlStr.append(" company_id = ? ");
//        Constant.REMIND_CLOSE_SQL(sqlStr,pcode,code,"t_crm_car");
//        sqlStr.append("AND ");
//        sqlStr.append(" vin IN ( ");
//        sqlStr.append("  SELECT ");
//        sqlStr.append("   vin ");
//        sqlStr.append("  FROM ");
//        sqlStr.append("   t_crm_car_workorder ");
//        sqlStr.append("  WHERE ");
//        sqlStr.append("   company_id = ? ");
//        sqlStr.append("        AND ");
//        sqlStr.append("   balance_date >= unix_timestamp(DATE_SUB(NOW(), INTERVAL 1 YEAR)) * 1000 ");
//        sqlStr.append("  AND real_amount> '0.00' ");
//        sqlStr.append("  GROUP BY ");
//        sqlStr.append("   vin ");
//        sqlStr.append("  HAVING ");
//        sqlStr.append("   count(*) >= 2 ");
//        sqlStr.append(" ) ");
//        sqlStr.append("AND vin NOT IN ( ");
//        sqlStr.append(" SELECT ");
//        sqlStr.append("  vin ");
//        sqlStr.append(" FROM ");
//        sqlStr.append("  t_crm_car_insurance ");
//        sqlStr.append(" WHERE ");
//        sqlStr.append("  contractstartinsurance > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 YEAR)) * 1000 ");
//        sqlStr.append(" and plate_num is not NULL   AND company_id = ? ");
//        sqlStr.append(") ");
//        sqlStr.append("AND DATE_FORMAT( ");
//        sqlStr.append(" DATE_SUB( ");
//        sqlStr.append("  FROM_UNIXTIME((CASE WHEN licence_date IS NULL THEN guarantee_start_date ELSE licence_date END) / 1000), ");
//        sqlStr.append("  INTERVAL ? DAY ");
//        sqlStr.append(" ), ");
//        sqlStr.append(" '%m' ");
//        sqlStr.append(") = DATE_FORMAT(now(), '%m'); ");
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), params.toArray());

    }

    /**
     * @param companyId 公司id
     * @param beforDays 提前天数
     * @return List 返回参数信息
     * @throws Exception
     * @desc 处理关爱类-生日提醒任务
     * @author liuze
     */
    @Override
    public List<Map<String, Object>> guanaishengri(String companyId, int beforDays, int start, int limit, String pcode, String code) throws Exception {
        List<Object> params = new ArrayList<>();
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select date_format(date_sub(FROM_UNIXTIME(owner_birthday/1000),INTERVAL ? DAY),'%m-%d')as expectdate," +
                "date_format(FROM_UNIXTIME(owner_birthday/1000),'%m-%d') as bday ,t_crm_car.* from t_crm_car where " +
                "date_format(date_sub(FROM_UNIXTIME(owner_birthday/1000),INTERVAL ? DAY),'%m') = date_format(now(),'%m') ");
        params.add(beforDays);
        params.add(beforDays);
        sqlStr.append(" and company_id = ? ");
        params.add(companyId);
//        sqlStr.append(" and EXISTS( select * from t_crm_car_base_detail detail where t_crm_car.vin=detail.vin and detail.company_id=?) ");
//        params.add(companyId);
        sqlStr.append("and " +
                "EXISTS( " +
                "select * from t_crm_car_workorder where real_amount>'0.00' and company_id=? " +
                "AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 " +
                "AND t_crm_car.id=car_id group by car_id  HAVING count(id)>0 " +
                ")");
        params.add(companyId);
        Constant.REMIND_CLOSE_SQL(sqlStr, pcode, code, "t_crm_car");
//        sqlStr.append("AND not EXISTS(select * from t_crm_car_remindclose cle where cle.user_carid=t_crm_car.id and cle.company_id=t_crm_car.company_id and cle.vin=t_crm_car.vin) ");

        sqlStr.append("limit ?,?");
        params.add(start);
        params.add(limit);
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), params.toArray());
    }


    /**
     * @param companyId 公司id
     * @param beforDays 提前天数
     * @return List 返回参数信息
     * @throws Exception
     * @desc 处理关爱类-质保提醒任务
     * @author liuze
     */
    @Override
    public List<Map<String, Object>> guanAiZhiBao(String companyId, int beforDays, int start, int limit, String pcode, String code) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT ");
        sqlStr.append(" DATE_FORMAT( ");
        sqlStr.append("  date_sub( ");
        sqlStr.append("   date_add( ");
        sqlStr.append("    FROM_UNIXTIME(guarantee_start_date / 1000), ");
        sqlStr.append("    INTERVAL 3 YEAR ");
        sqlStr.append("   ), ");
        sqlStr.append("   INTERVAL ? DAY ");
        sqlStr.append("  ), ");
        sqlStr.append("  '%Y-%m-%d' ");
        sqlStr.append(" ) AS txrq, DATE_FORMAT(date_add(FROM_UNIXTIME(guarantee_start_date / 1000),INTERVAL 3 YEAR),'%Y-%m-%d') as zbr,");
        sqlStr.append("DATE_FORMAT(FROM_UNIXTIME(guarantee_start_date / 1000),'%Y-%m-%d') as zbsj,");
        sqlStr.append(" company_id, ");
        sqlStr.append(" t_crm_car.* ");
        sqlStr.append("FROM ");
        sqlStr.append(" t_crm_car ");
        sqlStr.append("WHERE ");
        sqlStr.append(" date_format( ");
        sqlStr.append("  date_sub( ");
        sqlStr.append("   date_add( ");
        sqlStr.append("    FROM_UNIXTIME(guarantee_start_date / 1000), ");
        sqlStr.append("    INTERVAL 3 YEAR ");
        sqlStr.append("   ), ");
        sqlStr.append("   INTERVAL ? DAY ");
        sqlStr.append("  ), ");
        sqlStr.append("  '%Y-%m' ");
        sqlStr.append(" ) = date_format(now(), '%Y-%m') ");
        sqlStr.append("AND company_id = ? ");
        Constant.REMIND_CLOSE_SQL(sqlStr, pcode, code, "t_crm_car");
//        sqlStr.append("AND not EXISTS(select * from t_crm_car_remindclose cle where cle.user_carid=t_crm_car.id and cle.company_id=t_crm_car.company_id and cle.vin=t_crm_car.vin) ");
        //挂基盘

        sqlStr.append("limit ?,?");

        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), beforDays, beforDays, companyId, start, limit);
    }

    /**
     * @param companyId 公司id
     * @param beforDays 提前天数
     * @return List 返回参数信息
     * @throws Exception
     * @desc 处理关爱类-延保提醒任务
     * @author liuze
     */
    @Override
    public List<Map<String, Object>> guanAiYanBao(String companyId, int beforDays, int start, int limit, String pcode, String code) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT ");
        sqlStr.append(" DATE_FORMAT( ");
        sqlStr.append("  date_sub( ");
        sqlStr.append("   FROM_UNIXTIME(ex.restop_insurance / 1000), ");
        sqlStr.append("   INTERVAL ? DAY ");
        sqlStr.append("  ), ");
        sqlStr.append("  '%Y-%m-%d' ");
        sqlStr.append(" ) AS txrq, DATE_FORMAT(FROM_UNIXTIME(ex.restop_insurance / 1000),'%Y-%m-%d') as ybdq,");
        sqlStr.append(" ex.*,car.plate_num,car.run_mile ");
        sqlStr.append("FROM ");
        sqlStr.append(" t_crm_car_exsurance ex,t_crm_car car ");
        sqlStr.append("WHERE ");
        sqlStr.append(" ex.company_id=? and ex.vin=car.vin  and ");
        sqlStr.append(" DATE_FORMAT( ");
        sqlStr.append("  date_sub( ");
        sqlStr.append("   FROM_UNIXTIME(ex.restop_insurance / 1000), ");
        sqlStr.append("   INTERVAL ? DAY ");
        sqlStr.append("  ), ");
        sqlStr.append("  '%Y-%m' ");
        sqlStr.append(" ) = date_format(now(), '%Y-%m') ");
        Constant.REMIND_CLOSE_SQL(sqlStr, pcode, code, "car");
//        sqlStr.append(" AND t_crm_car_exsurance.company_id = ? and t_crm_car_exsurance.vin =  t_crm_car.vin " +
//                "AND NOT EXISTS (SELECT * FROM t_crm_car_remindclose cle WHERE cle.user_carid = t_crm_car.id AND cle.company_id = t_crm_car.company_id AND cle.vin = t_crm_car.vin) ");

        //延保挂基盘
        sqlStr.append("limit ?,?");
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), beforDays, companyId, beforDays, start, limit);
    }


    /**
     * @param vin  车驾号
     * @param type 处理类型
     * @return List 返回参数信息
     * @throws Exception
     * @desc 根据车牌信息处理流失类-提醒日期
     * @author liuze
     */
    public String liuShiTxrq(String vin, int type,String companyid) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT ");
        sqlStr.append(" DATE_FORMAT( ");
        sqlStr.append("  DATE_ADD( ");
        sqlStr.append("   from_unixtime(balance_date / 1000), ");
        sqlStr.append("   INTERVAL ? MONTH ");
        sqlStr.append("  ), ");
        sqlStr.append("  '%Y-%m-%d' ");
        sqlStr.append(" ) as txrq ");
        sqlStr.append("FROM ");
        sqlStr.append(" t_crm_car_workorder ");
        sqlStr.append("WHERE ");
        sqlStr.append(" vin = ? and real_amount > '0.00' and company_id=?");

//        sqlStr.append(" AND DATE_FORMAT( " +
//                "  DATE_ADD( " +
//                "   from_unixtime(balance_date / 1000), " +
//                "   INTERVAL ? MONTH " +
//                "  ), " +
//                "  '%Y%m' " +
//                " ) =DATE_FORMAT(NOW(),'%Y%m') ");
        sqlStr.append(" ORDER BY ");
        sqlStr.append(" balance_date DESC ");
        sqlStr.append("LIMIT 1;");

        Map m = this.getJdbcDao().queryForMapBySqlWithParams(sqlStr.toString(), type, vin,companyid);
        if (m.get("txrq") != null)
            return m.get("txrq").toString();
        return null;
    }

    /****
     * 查询车辆流失前最后进场日期
     * 维修按结算金额
     * 索赔按维修金额
     * @param plat_num
     * @return
     * @throws Exception
     */
    public String liuShiRq(String company_id, String plat_num) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT " +
                " DATE_FORMAT( " +
                "  FROM_UNIXTIME(balance_date / 1000), " +
                "  '%Y-%m-%d' " +
                " ) AS lsrq " +
                "FROM " +
                " t_crm_car_workorder " +
                "where company_id = ? and plate_num = ? and real_amount>'0.00'  " +
                "ORDER BY " +
                " open_date DESC " +
                "LIMIT 1");

        Map m = this.getJdbcDao().queryForMapBySqlWithParams(sqlStr.toString(), company_id, plat_num);
        if (m.get("lsrq") != null)
            return m.get("lsrq").toString();
        return "";
    }


    /**
     * @param company_id 公司id
     * @param type       处理类型
     * @return List 返回参数信息
     * @throws Exception
     * @desc 处理流失类信息
     * @author liuze
     */
    @Override
    public List<Map<String, Object>> liuShi(String company_id, int type, int beforeDay, int start, int limit, String typePCode) throws Exception {
        String nowStr = DateUtils.getTimeStr(new Date(), "yyyy-MM-dd");
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT ");
        sqlStr.append(" * ");
        sqlStr.append("FROM ");
        sqlStr.append(" t_crm_car ");
        sqlStr.append("WHERE company_id = ?  ");
        //排除关闭客户的
        Constant.REMIND_CLOSE_SQL(sqlStr, typePCode, String.valueOf(type), "t_crm_car");
        sqlStr.append("and vin IN ( ");
        sqlStr.append("  SELECT DISTINCT ");
        sqlStr.append("   vin ");
        sqlStr.append("  FROM ");
        sqlStr.append("   t_crm_car_workorder ");
        sqlStr.append("  WHERE ");
//        sqlStr.append("     NOT EXISTS (SELECT * FROM  t_crm_car_remindclose cle  WHERE  cle.user_carid = car_id ) AND ");
        sqlStr.append("   balance_date BETWEEN unix_timestamp( ");
        sqlStr.append("    DATE_SUB(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'),INTERVAL " + beforeDay + " DAY), INTERVAL ? MONTH) ");
        sqlStr.append("   ) * 1000 ");
        sqlStr.append("  AND unix_timestamp( ");
        sqlStr.append("   DATE_SUB(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'),INTERVAL " + beforeDay + " DAY), INTERVAL ? MONTH) ");
        sqlStr.append("  ) * 1000 ");
        sqlStr.append("  AND vin NOT IN ( ");
        sqlStr.append("   SELECT DISTINCT ");
        sqlStr.append("    vin ");
        sqlStr.append("   FROM ");
        sqlStr.append("    t_crm_car_workorder ");
        sqlStr.append("   WHERE ");
        sqlStr.append("    balance_date >= unix_timestamp( ");
        sqlStr.append("    DATE_SUB(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'),INTERVAL " + beforeDay + " DAY), INTERVAL ? MONTH) ");
        sqlStr.append("    ) * 1000 ");
        sqlStr.append("   AND real_amount> '0.00' ");
        sqlStr.append("  ) ");
        sqlStr.append(" ) limit ?,?");
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), company_id, type
                , type - 1, type - 1, start, limit);
    }

    @Override
    public List<Map<String, Object>> liuShiIsEmpty(String company_id, int type, int beforeDay, int start, int limit, String typePCode) throws Exception {
        String nowStr = DateUtils.getTimeStr(new Date(), "yyyy-MM-dd");
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT ");
        sqlStr.append(" * ");
        sqlStr.append("FROM ");
        sqlStr.append(" t_crm_car ");
        sqlStr.append("WHERE company_id = ?  ");
        //排除关闭客户的
        Constant.REMIND_CLOSE_SQL(sqlStr, typePCode, String.valueOf(type), "t_crm_car");
        sqlStr.append("and vin IN ( ");
        sqlStr.append("  SELECT DISTINCT ");
        sqlStr.append("   vin ");
        sqlStr.append("  FROM ");
        sqlStr.append("   t_crm_car_workorder ");
        sqlStr.append("  WHERE ");
//        sqlStr.append("     NOT EXISTS (SELECT * FROM  t_crm_car_remindclose cle  WHERE  cle.user_carid = car_id ) AND ");
        sqlStr.append("   balance_date BETWEEN unix_timestamp( ");
        sqlStr.append("   DATE_SUB(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'),INTERVAL " + beforeDay + " DAY), INTERVAL ? MONTH) ");
        sqlStr.append("   ) * 1000 ");
        sqlStr.append("  AND unix_timestamp( ");
        sqlStr.append("   DATE_SUB(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'),INTERVAL " + beforeDay + " DAY), INTERVAL ? MONTH) ");
        sqlStr.append("  ) * 1000 ");
        sqlStr.append("  AND vin NOT IN ( ");
        sqlStr.append("   SELECT DISTINCT ");
        sqlStr.append("    vin ");
        sqlStr.append("   FROM ");
        sqlStr.append("    t_crm_car_workorder ");
        sqlStr.append("   WHERE ");
        sqlStr.append("    balance_date >= unix_timestamp( ");
        sqlStr.append("     DATE_SUB(DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'),INTERVAL " + beforeDay + " DAY), INTERVAL ? MONTH) ");
        sqlStr.append("    ) * 1000 ");
        sqlStr.append("   AND real_amount>'0.00' ");
        sqlStr.append("  ) ");
        sqlStr.append(" ) limit ?,?");
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), company_id, type
                , type - 1, type - 1, start, limit);
    }


    /**
     * @param company_id 公司id
     * @param start      开始参数
     * @param limit      每页数据量
     * @return List 返回参数信息
     * @throws Exception
     * @desc 分页查询车辆表所有信息
     * @author liuze
     */
    @Override
    public List<Map<String, Object>> pageForCars(String company_id, int start, int limit, String typeCode, String code) throws Exception {
        List<String> parpams = new ArrayList<>();
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select * from t_crm_car where  company_id = ? and vin='LSGUA83B3BE067955' ");
        parpams.add(company_id);
        //车辆信息必须包含在基盘内
//        sqlStr.append(" and EXISTS( select * from t_crm_car_base_detail detail where t_crm_car.vin=detail.vin and detail.company_id=?) ");
//        parpams.add(company_id);
        sqlStr.append("and   EXISTS( " +
                "select * from t_crm_car_workorder where company_id=? and real_amount>'0.00'  " +
                "AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 " +
                "AND t_crm_car.id=car_id group by car_id  HAVING count(id)>0) ");
        parpams.add(company_id);
        Constant.REMIND_CLOSE_SQL(sqlStr, typeCode, code, "t_crm_car");
        Map<String, Object> mp = this.page(sqlStr.toString(), parpams.toArray(), String.valueOf(start), String.valueOf(limit));
        if (mp.get("dataList") == null)
            return null;
        return (List<Map<String, Object>>) mp.get("dataList");
    }


    /**
     * 年审优化后的执行代码
     *
     * @param startTime
     * @param endTime
     * @param companyId
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> nianShenPageForCars(String startTime, String endTime, String companyId, int start, int limit) throws Exception {
        List<String> list = new ArrayList<>();
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select * from (" +
                "SELECT " +
                "case " +
                " WHEN temp1.nYear>15 then DATE_ADD(DATE_ADD(temp1.nianShenDate,INTERVAL -6 MONTH),INTERVAL -30 DAY) " +
                " WHEN (temp1.nYear>6 AND temp1.nYear<=15) THEN DATE_ADD(temp1.nianShenDate,INTERVAL -30 DAY) " +
                " WHEN mod(temp1.nYear,2) =0 THEN DATE_ADD(temp1.nianShenDate,INTERVAL -30 DAY) " +
                " ELSE ''" +
                " end  expect_date, temp1.* " +
                "FROM ( " +
                "SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT( FROM_UNIXTIME(guarantee_start_date / 1000), '%Y' ) AS nYear, " +
                "CONCAT( DATE_FORMAT(NOW(), '%Y'), '-', DATE_FORMAT( FROM_UNIXTIME(guarantee_start_date / 1000), '%m-%d' ) ) AS nianShenDate, " +
                "DATE_FORMAT( FROM_UNIXTIME(guarantee_start_date / 1000), '%Y-%m-%d' ) zDate, id as car_id, company_id, plate_num, vin, " +
                "guarantee_start_date, licence_date,contact_tel " +
                "FROM t_crm_car " +
                "WHERE company_id = ? " +
                "AND EXISTS ( " +
                "SELECT * FROM t_crm_car_workorder WHERE " +
                "real_amount > '0.00' AND balance_date >= unix_timestamp( DATE_SUB(NOW(), INTERVAL 1 YEAR) ) * 1000 " +
                "AND t_crm_car.id = car_id GROUP BY car_id HAVING count(id) > 0 ) " +
                "AND NOT EXISTS ( " +
                "SELECT * FROM t_crm_car_remindclose cle WHERE " +
                "cle.user_carid = t_crm_car.id AND cle.company_id = t_crm_car.company_id AND cle.vin = t_crm_car.vin ) " +
                ") temp1 where temp1.nYear>0 ) temp2 where 1=1 and ");
        list.add(companyId);
        if (!"".equals(startTime) && !"".equals(endTime)) {
            sqlStr.append("temp2.expect_date>=? and temp2.expect_date<=? ");
            list.add(startTime);
            list.add(endTime);
        } else {
            sqlStr.append(" AND DATE_FORMAT(temp2.expect_date,'%M')=DATE_FORMAT(NOW(),'%M')  ");
        }
        Map<String, Object> mp = this.page(sqlStr.toString(), list.toArray(), String.valueOf(start), String.valueOf(limit));
        if (mp.get("dataList") == null)
            return null;
        return (List<Map<String, Object>>) mp.get("dataList");
    }


    /****
     * 处理保险类别-次年／续保／接待类任务
     * 根据类型（次年，续保，接待）／保险类别（交强险，商业险）查询保险表中生效日期+1年减去提前提醒日期判断是否与当年月一致
     * 一致则查询车辆信息插入任务表
     * @param company_id 公司id
     * @param adv 提醒提前天数
     * @param basetype 处理类型／次年，续保，接待
     * @param type 保险类型，0-交强险，1-商业险
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> baoxian(String company_id, int adv, String basetype, int type, String typePcode, String typeCode) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        sqlStr.append("SELECT ");
        sqlStr.append(" DATE_FORMAT( ");
        sqlStr.append("  DATE_SUB( ");
        sqlStr.append("   DATE_ADD( ");
        if (type == 0) {
            sqlStr.append("    FROM_UNIXTIME(compulsory_start_date / 1000), ");
        } else {
            sqlStr.append("    FROM_UNIXTIME(contractstartinsurance / 1000), ");
        }
        sqlStr.append("    INTERVAL 1 YEAR ");
        sqlStr.append("   ), ");
        sqlStr.append("   INTERVAL ? DAY ");
        params.add(adv);
        sqlStr.append("  ), ");
        sqlStr.append("  '%Y-%m-%d' ");
        if (type == 0) {
            sqlStr.append(" )as txrq,DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(compulsory_start_date / 1000),INTERVAL 1 YEAR),'%Y-%m-%d') AS dqrq,t_crm_car_insurance.* ");
        } else {
            sqlStr.append(" )as txrq,DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(contractstartinsurance / 1000),INTERVAL 1 YEAR),'%Y-%m-%d') AS dqrq,t_crm_car_insurance.* ");
        }
        sqlStr.append("FROM ");
        sqlStr.append(" t_crm_car_insurance ");
        sqlStr.append("WHERE ");
        sqlStr.append("company_id = ? ");
        params.add(company_id);
        Constant.REMIND_CLOSE_SQL(sqlStr, typePcode, typeCode, "t_crm_car_insurance");
//        sqlStr.append(" not EXISTS(select * from t_crm_car_remindclose cle where cle.user_carid=t_crm_car_insurance.user_carid and " +
//                "cle.company_id=t_crm_car_insurance.company_id and cle.vin=t_crm_car_insurance.vin) " +
//                "AND ");
        sqlStr.append(" and DATE_FORMAT( ");
        sqlStr.append("  DATE_SUB( ");
        sqlStr.append("   DATE_ADD( ");
        if (type == 0) {//交强险
            sqlStr.append("    FROM_UNIXTIME(compulsory_start_date / 1000), ");
        } else {//商业险
            sqlStr.append("    FROM_UNIXTIME(contractstartinsurance / 1000), ");
        }
        sqlStr.append("    INTERVAL 1 YEAR ");
        sqlStr.append("   ), ");
        sqlStr.append("   INTERVAL ? DAY ");
        params.add(adv);
        sqlStr.append("  ), ");
        sqlStr.append("  '%Y%m' ");
        sqlStr.append(" ) = DATE_FORMAT(now(), '%Y%m') ");
        if (!Constant.INSURANCE_TYPE.RECEPTION.equals(basetype)) {
            //新保
            if (Constant.INSURANCE_TYPE.NEW_INSURANCE.equals(basetype)) {
                sqlStr.append(" AND type=1 ");
            } else if (Constant.INSURANCE_TYPE.RENEWAL.equals(basetype)) {
                sqlStr.append(" AND type>1 ");
            }
//            sqlStr.append("AND base_type LIKE '%" + basetype + "%' ");
        }

        System.out.println(sqlStr);
        List list = this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), params.toArray());
        return list;
    }

    // for controller
    public Map query(String start, String limit, String cid, String pcode, String code, String status, String pdsql) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT  ");
        sqlStr.append("  *  ");
        sqlStr.append("FROM  ");
        sqlStr.append("  t_crm_task  ");
        sqlStr.append("WHERE  ");
        sqlStr.append("  company_id = ?  ");
        List<String> ps = new ArrayList<>();
        ps.add(cid);
        if (!StringUtils.isEmpty(pcode)) {
            sqlStr.append(" AND type_pcode = ?  ");
            ps.add(pcode);
        }

        if (!StringUtils.isEmpty(code)) {
            sqlStr.append(" AND type_code = ?");
            ps.add(code);
        }
        if (!StringUtils.isEmpty(status)) {
            sqlStr.append(" and status = ?");
            ps.add(status);
        }
        if (!StringUtils.isEmpty(pdsql)) {
//            sqlStr.append(" and ");
            sqlStr.append(pdsql);
        }
        System.out.println(sqlStr.toString());
        System.out.println(sqlStr);
        System.out.println(ps.toArray().length);
        for (int i = 0; i < ps.toArray().length; i++) {
            System.out.println(ps.toArray()[i]);
        }
        return this.page(sqlStr.toString(), ps.toArray(), start, limit);
    }


    public List<Map<String, Object>> findRoleByUid(String uid) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT ");
        sqlStr.append(" * ");
        sqlStr.append("FROM ");
        sqlStr.append(" t_bc_role_user ");
        sqlStr.append("WHERE ");
        sqlStr.append(" u_id = ?");
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), uid);
    }

    @Override
    public Map<String, String> findCodeByRId(String company_id, String rid, String type) throws Exception {
        Map<String, String> map = new HashMap<>();
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT ");
        sqlStr.append("  *  ");
        sqlStr.append("FROM  ");
        sqlStr.append("  t_crm_task_conf  ");
        sqlStr.append("WHERE  ");
        sqlStr.append("  company_id = ?  and status = '1' and task_type=? ");
        //---------------------增加角色操作权限过滤，查看任务只需要查询type='0' by 王帅2017-04-07 19:34
        sqlStr.append("and role_id = ?");
        List<Map<String, Object>> datas = this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr.toString(), company_id, type, rid);
        if (datas == null || datas.size() == 0) {
            return map;
        }
        for (Map<String, Object> data : datas) {
            if (data.get("task_c_code") == null) {
                continue;
            }
            String[] codes = data.get("task_c_code").toString().split(",");
            for (String code : codes) {
                if (code.length() > 0) {
                    map.put(code, "1");
                }
            }


        }
        return map;

    }

    /**
     * 根据公司ID 车辆ID code值查看是否存在相应任务
     *
     * @param companyId 公司ID
     * @param carId     车辆ID
     * @param pcode     任务大类
     * @param code      任务小类
     * @return
     */

    private List<Map<String, Object>> findTasksByCarId(String companyId, String carId, String pcode, String code) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "car_id=? and " +
                "type_pcode=? and " +
                "type_code=?");
        return this.list(sql.toString(), companyId, carId, pcode, code);
    }

    private List<Map<String, Object>> findTasksByVin(String companyId, String vin, String pcode, String code) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "vin=? and " +
                "type_pcode=? and " +
                "type_code=?");
        return this.list(sql.toString(), companyId, vin, pcode, code);
    }

    /**
     * 根据车辆所在公司、车架号、查询任务是否存在
     * @param companyId
     * @param vin
     * @param carId
     * @param pcode
     * @param code
     * @return
     * @throws Exception
     */
    public List<Map<String,Object>> findTaskByCarIdAndVin(String companyId,String vin,String carId,String pcode,String code) throws Exception {
        if(!"".equals(vin)){
            return this.findTasksByVin(companyId,vin,pcode,code);
        }
        return this.findTasksByCarId(companyId,carId,pcode,code);
    }

    @Override
    public List<Map<String, Object>> findTasks(String companyId, String vin, String pcode, String code, String expectDate) throws Exception {

        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "vin =? and " +
                "type_pcode=? and " +
                "type_code=?  and " +
                "DATE_FORMAT(expect_date,'%Y%m%d')=DATE_FORMAT(?,'%Y%m%d')");
        return this.list(sql.toString(), companyId, vin, pcode, code, expectDate);
    }

    @Override
    public List<Map<String, Object>> findTasksByVin(String companyId, String vin, String pcode, String code, String expectDate) throws Exception {

        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "vin=? and " +
                "type_pcode=? and " +
                "type_code=?  and " +
                "DATE_FORMAT(expect_date,'%Y%m%d')=DATE_FORMAT(?,'%Y%m%d')");
        return this.list(sql.toString(), companyId, vin, pcode, code, expectDate);
    }

    public List<Map<String, Object>> findTasksByCarId(String companyId, String carId, String pcode, String code, String expectDate) throws Exception {

        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "car_id=? and " +
                "type_pcode=? and " +
                "type_code=?  and " +
                "DATE_FORMAT(expect_date,'%Y%m%d')=DATE_FORMAT(?,'%Y%m%d')");
        return this.list(sql.toString(), companyId, carId, pcode, code, expectDate);
    }

    /**
     * 处理车辆回厂任务
     *
     * @param sqlStr sql
     * @param params 参数信息
     * @throws Exception
     */
    @Override
    public Integer carBackToFacTask(String sqlStr, List<Object> params) throws Exception {
        return this.getJdbcDao().update(sqlStr, params.toArray());
    }

    /**
     * 根据公司信息 获得任务信息
     *
     * @param companyId 公司ID
     * @param start     起始记录数
     * @param limit     采集记录数
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> findTaskByComapnyId(String companyId, int start, int limit) throws Exception {
        String sqlStr = "" +
                "select task.*,taskRef.role_id from t_crm_task task " +
                "LEFT JOIN " +
                "t_crm_task_conf taskRef " +
                "ON " +
                " task.type_code=taskRef.task_c_code and task.company_id=taskRef.company_id " +
                "where task.company_id=? and (task.sa_id='' or task.sa_id is null) limit ?,?";
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr, companyId, start, limit);
    }

    /**
     * 根据公司ID 角色ID 获得其色所配置的车牌信息
     *
     * @param roleId    角色ID
     * @param companyId 公司ID
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> findRoleTypeByRoleId(String roleId, String companyId) throws Exception {
        String sqlStr = "select * from t_crm_car_ref where role_id=? and company_id=? order by type DESC";
        return this.getJdbcDao().queryForMapListBySqlWithParams(sqlStr, roleId, companyId);
    }

    public List<Map<String, Object>> findTaskProcessingSa(String compainyId, String lastStr, String lastNum) {
        String sqlStr = "select * from t_crm_car_ref where FIND_IN_SET('1',`value`) or FIND_IN_SET('8',`value`)";
        return null;
    }

    /**
     * 查询任务详情
     *
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public TCrmTask findTaskById(String id) throws Exception {
        return (TCrmTask) this.getHibernateDao().findEntity(TCrmTask.class, id);
    }

    /**
     * 更新任务信息
     *
     * @param list
     * @throws Exception
     */
    @Override
    public void updateEntiry(List<TCrmTask> list) throws Exception {
        this.getHibernateDao().updateEntityList(list);
    }

    @Override
    public Map<String, Object> findTransferTaskList(String start, String limit, String sql, List<Object> paramlist) throws Exception {
        Map<String, Object> map = this.getJdbcDao().queryForPageMap(start, limit, sql, paramlist.toArray());
        return map;
    }

    /**
     * 批量更新车辆SAID
     *
     * @param lists
     */
    @Override
    public void updBatchCrmTaskSaId(final List<TCrmTask> lists) {
        String sql = "update t_crm_task_real set sa_id=? where id=?";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                TCrmTask tCrmTask = lists.get(i);
                ps.setString(1, tCrmTask.getSaId());
                ps.setString(2, tCrmTask.getId());
            }

            @Override
            public int getBatchSize() {
                return lists.size();
            }
        });
    }

    @Override
    public void cleanTask(String companyId) throws Exception {
        String sql = "delete from t_crm_task where company_id=?";
        this.executeSQL(sql, companyId);
    }

    /**
     * 根据公司ID 车辆ID 大类、小类以及执行时间 查询任务是否已经存在
     *
     * @param companyId
     * @param carId
     * @param code
     * @param pCode
     * @return
     * @throws Exception
     */
    @Override
    public boolean checkSbTask(String companyId, String carId, String code, String pCode, String expectDate) throws Exception {
        List<Map<String, Object>> lists = this.findTasks(companyId, carId, pCode, code, expectDate);
        if (lists != null) {
            if (lists.size() > 0) {
                return true;
            }
        }
        return false;
    }

    @Override
    public boolean checkTaskByVinAndId(String companyId, String vin,String carId, String code, String pCode, String expectDate) throws Exception {
        List<Map<String, Object>> lists=null;
        if(!"".equals(StringUtils.isEmpty(vin))){
            lists= this.findTasksByVin(companyId, vin, pCode, code, expectDate);
        }else{
            lists= this.findTasksByCarId(companyId,carId,pCode,code,expectDate);
        }
        if (lists != null) {
            if (lists.size() > 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * 根据公司ID 车辆ID 大类、小类以、执行时间以及提醒内容 查询券卡任务任务是否已经存在
     *
     * @param companyId
     * @param carId
     * @param code
     * @param pCode
     * @param expectDate
     * @param content
     * @return
     * @throws Exception
     */
    @Override
    public boolean checkcouponTask(String companyId, String carId, String code, String pCode, String expectDate, String content) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "car_id=? and " +
                "type_pcode=? and " +
                "type_code=?  and " +
                "content=? and " +
                "DATE_FORMAT(expect_date,'%Y%m%d')=DATE_FORMAT(?,'%Y%m%d')");
        List<Map<String, Object>> lists = this.getJdbcDao().queryForMapListBySqlWithParams(sql.toString(), companyId, carId, pCode, code, content, expectDate);
        if (lists != null) {
            if (lists.size() > 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * 流失类任务检测任务是否存在
     *
     * @param companyId
     * @param carId
     * @param code
     * @param pCode
     * @return
     * @throws Exception
     */
    @Override
    public boolean checkLsTask(String companyId, String carId, String code, String pCode) throws Exception {
        StringBuffer sql = new StringBuffer();
        List<String> params = new ArrayList<>();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "car_id=? and " +
                "type_pcode=? and " +
                "type_code >=?  ");
        params.add(companyId);
        params.add(carId);
        params.add(pCode);
        params.add(code);
        List<Map<String, Object>> list = this.getJdbcDao().queryForMapListBySqlWithParams(sql.toString(), params.toArray());
        if (list != null) {
            if (list.size() > 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * 流失任务检测 改为vin
     * @param companyId
     * @param vin
     * @param code
     * @param pCode
     * @return
     * @throws Exception
     */
    @Override
    public boolean checkLsTaskByVin(String companyId, String vin, String code, String pCode) throws Exception {
        StringBuffer sql = new StringBuffer();
        List<String> params = new ArrayList<>();
        sql.append("select * from t_crm_task where ");
        sql.append("company_id=? and " +
                "vin=? and " +
                "type_pcode=? and " +
                "type_code >=?  ");
        params.add(companyId);
        params.add(vin);
        params.add(pCode);
        params.add(code);
        List<Map<String, Object>> list = this.getJdbcDao().queryForMapListBySqlWithParams(sql.toString(), params.toArray());
        if (list != null) {
            if (list.size() > 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * 流失六个月检测一个月以内如果有保养任务时不在生成流失任务
     *
     * @return true 说明已经存在不允许在次添加 false说明不存在可以进行添加
     */
    @Override
    public boolean checkLsByBYTask(String companyId, String carid, String code, String pCode, Long lxtxrq) throws Exception {
        if ("6".equals(code)) {
            Long beforeMonthTxrq = DateUtils.addMonthLongTime(lxtxrq, -1);
            StringBuffer sql = new StringBuffer();
            List<String> params = new ArrayList<>();
            sql.append(" select id, create_time, create_user, update_time, update_user, vin, car_id, company_id, contact_id, content, deal_date, expect_date, is_back, link_man, link_phone, plateNum, sa_id,status,type_code, type_pcode from t_crm_task where " +
                    " type_pcode='by' and expect_date>=? and expect_date<=? and company_id=? and car_id=?  ");
            params.add(DateUtils.formatLongTimeByYYYYMMDD(beforeMonthTxrq));
            params.add(DateUtils.formatLongTimeByYYYYMMDD(lxtxrq));
            params.add(companyId);
            params.add(carid);
            sql.append(" UNION " +
                    " select id, create_time, create_user, update_time, update_user, vin, car_id, company_id, contact_id, content, deal_date, expect_date, is_back, link_man, link_phone, plateNum, sa_id,status,type_code, type_pcode from t_crm_task_real where " +
                    " type_pcode='by' and expect_date>=? and expect_date<=? and company_id=? and car_id=? ");
            params.add(DateUtils.formatLongTimeByYYYYMMDD(beforeMonthTxrq));
            params.add(DateUtils.formatLongTimeByYYYYMMDD(lxtxrq));
            params.add(companyId);
            params.add(carid);
            List<Map<String, Object>> list = this.list(sql.toString(), params.toArray());
            if (list.size() > 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * 根据ID与规则信息 查询用户未处理完成的任务数量
     *
     * @param sql
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Integer findTaskCount(String sql, String uid) throws Exception {
        Map<String, Object> map = this.getJdbcDao().queryForMapBySqlWithParams(sql, uid);
        if (map != null) {
            if (map.get("count") != null) {
                return Integer.valueOf(String.valueOf(map.get("count")));
            }
        }
        return 0;
    }

    /**
     * 根据用户ID 获得用户信息
     *
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> findUserById(String uid) throws Exception {
        Map<String, Object> map = this.getJdbcDao().queryForMapBySqlWithParams("select * from t_bc_user where id=?", uid);
        if (map != null) {
            if (map.size() > 0) {
                return map;
            }
        }
        return null;
    }

    //-----------------------

    /**
     * 统计临时表分类下任务数量
     *
     * @param companyId
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> taskCountByPcode(String companyId) throws Exception {
        String sql = "select count(id) counts,type_pcode from ( " +
                "select id,CASE type_pcode WHEN 'bx' THEN 'bx'  WHEN 'bxjd' THEN 'bx'  WHEN 'bxcn' THEN 'bx'  WHEN 'bxxb' THEN 'bx'   WHEN 'bxhy' THEN 'bx'  " +
                "WHEN 'bxfhy' THEN 'bx'  ELSE type_pcode  END as type_pcode  from t_crm_task where company_id=? ) t group by type_pcode";
        List<Map<String, Object>> list = this.getJdbcDao().queryForMapListBySqlWithParams(sql, companyId);
        if (list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> taskCountByCode(String companyId) throws Exception {
        String sql = "SELECT " +
                "count(id) counts, " +
                "CASE type_pcode " +
                "    WHEN 'bx' THEN 'bx' " +
                "WHEN 'bxjd' THEN 'bx' " +
                "WHEN 'bxcn' THEN 'bx' " +
                "WHEN 'bxxb' THEN 'bx'  " +
                "WHEN 'bxhy' THEN 'bx'" +
                "WHEN 'bxfhy' THEN 'bx' " +
                "ELSE type_pcode " +
                "END " +
                "as type_pcode, " +
                "type_code FROM t_crm_task WHERE " +
                "company_id =?  GROUP BY type_code ORDER BY type_code ASC";
        List<Map<String, Object>> list = this.getJdbcDao().queryForMapListBySqlWithParams(sql, companyId);
        if (list.size() > 0) {
            return list;
        }
        return null;
    }

    /**
     * 获得任务详情信息
     *
     * @param taskId
     * @return
     * @throws Exception
     */
    @Override
    public TCrmTaskReal getTaskRealById(String taskId) throws Exception {
        String hql = "from TCrmTaskReal where id=:id";
        Map<String, Object> params = new HashMap<>();
        params.put("id", taskId);
        return (TCrmTaskReal) this.getHibernateDao().findEntityByHqlWithParams(hql, params);
    }

    /**
     * 获得任务详情
     *
     * @param typePcode
     * @param vin
     * @return
     * @throws Exception
     */
    @Override
    public TCrmTaskReal getTaskRealByTypdCode(String typePcode, String vin) throws Exception {
        String hql = "from TCrmTaskReal where vin=:vin and typePCode=:typePCode group by vin";
        Map<String, Object> params = new HashMap<>();
        params.put("vin", vin);
        params.put("typePCode", typePcode);
        return (TCrmTaskReal) this.getHibernateDao().findEntityByHqlWithParams(hql, params);
    }
}
